import re
import pymysql

fd = open('txt/2018.txt','r',encoding='utf-8')
data = fd.read()
fd.close()

pattern = re.compile(r'第一作者.*')
info_list = pattern.findall(data)

result_dirt = {}

for item in info_list:
    pattern_name = re.compile(r'：[\u4e00-\u9fa5]+')
    res_name = pattern_name.findall(item)
    pattern_email = re.compile(r'(\w[-\w.+]+@([A-Za-z0-9][-A-Za-z0-9]+\.)+[A-Za-z]{2,14})')
    res_email = pattern_email.findall(item)
    for i in range(len(res_name)):
        result_dirt[res_name[i].replace('：','')] = res_email[i][0]
    
#获取数据库链接
connection = pymysql.connect(host = '',
                            user = '',
                            password = '',
                            db = '',
                            charset = 'utf8')

try:
    #获取会话指针
    with connection.cursor() as cursor:
        for key,value in result_dirt.items():
            #修改sql语句
            sql = "update author_info set a_email='"+value+"' where a_name='"+key+"'"
            #执行sql语句
            cursor.execute(sql)

        #提交数据库
        connection.commit()
finally:
    connection.close()